<?php defined ( 'SYSPATH' ) or die ( 'No direct access allowed.' );
/**
 * 产品近一个月销量统计报表
 * Enter description here ...
 * @author locove
 *
 */
class Controller_Report_Sale extends Controller_Admin
{
    public function before()
    {
    	try {
    		  ACL::Required('sale report');
    	} catch ( HTTP_Exception_403 $e ) {
				Message::error('sorry，你没有权限进这个目录');
    		$this->request->redirect ( URL::base (true) );
    	}
        parent::before();
    }
    /**
     * 首页搜索界面
     * Enter description here ...
     */
	public function action_index()
	{
		$this->title=__("产品月销量统计");
		$view = View::factory('report/sale');
		$this->response->body($view);
	}
	
	/**
	 *获取分页产品销量信息  
	 */
	public function action_sale_load()
	{
		$offset=0;
		$items_per_page=100;
	 	$and_where=$orderby='';
		if(isset($_GET['page'])&&$_GET['page']){
	 		$offset=($_GET['page']-1)*$items_per_page;
		}
	 	if(isset($_GET['sku'])&&$_GET['sku']){
	 		$and_where=" AND sku='".$_GET['sku']."'"; 
	 	}
	 	if(isset($_GET['sortcondition'])&&$_GET['sortcondition']){
	 		$order=" order by ".$_GET['sortcondition']." desc";
	 		if(isset($_GET['order'])&&$_GET['order']){
		 		$order=$_GET['order']==1?'asc':'desc';
	 			$orderby=" order by ".$_GET['sortcondition']." ".$order;
	 		}
	 	}
	 	$sql="select count(*) as num from product_attached where 1 $and_where";
	 	$count=DB::query(Database::SELECT, $sql)->execute('readonlyerp')->current();
 		$pagination = Pagination::factory(array(
            'items_per_page' => $items_per_page, 
            'total_items' => $count['num'],
	        'uri'=> $this->request->uri(),
        ));
		$sql="select * from product_attached where 1 $and_where $orderby limit $offset,$items_per_page";
	 	$sales=DB::query(Database::SELECT, $sql)->execute('readonlyerp')->as_array();


	 	if(count($sales)>0){
		 	foreach($sales as $key=>$sale){
                //START     ----------------------------------CWY更新代码-------------------------------------

                /**
                 * 采购最低价
                 */
                $purchase_price = Newerpclientapi::getSupplierskuPrice($sale['sku']);
                if(isset($purchase_price[0]['price'])){
                    $sales[$key]['min_purchase_price']=$purchase_price[0]['price'];
                }else{
                    $sales[$key]['min_purchase_price']=0;
                }
                $min=Skusystemapi::getSku($sale['sku']);
                if(!empty($min)){
                    $price = Skusystemapi::getSkuPrice($sale['sku']);
                    $cnname = Skusystemapi::getSkuCnname($sale['sku']);
                    $sales[$key]['purchase_price']=$price[0]['num'];//公司定价
                    $sales[$key]['sku_name']=$cnname[0]['name'];//公司SKU名称
                }else{
                    $sales[$key]['purchase_price']='';
                    $sales[$key]['sku_name']='';
                }
//                $sql="select price from erp_providerproducts where iproductID='".$sale['sku']."' order by price ASC limit 1";
//	        	$purchase_price=DB::query(Database::SELECT, $sql)->execute('readonlyerp')->current();
//	        	if(isset($purchase_price['price'])){
//		        	$sales[$key]['min_purchase_price']=$purchase_price['price'];
//	        	}else{
//	        		$sales[$key]['min_purchase_price']=0;
//	        	}
//	        	$sql="select products_name_cn,products_values from erp_products_data where products_sku='".$sale['sku']."' limit 1";
//	        	$min=DB::query(Database::SELECT, $sql)->execute('readonlyerp')->current();
//	        	if(isset($min['products_values'])){
//		        	$sales[$key]['purchase_price']=$min['products_values'];
//		        	$sales[$key]['sku_name']=$min['products_name_cn'];
//	        	}else{
//	        		$sales[$key]['purchase_price']='';
//	        		$sales[$key]['sku_name']='';
//	        	}
                //END     ----------------------------------CWY更新代码-------------------------------------
	        }
        }
 		$view=View::factory('report/sale_table')
 			->set('sales',$sales)
 			->set('totles',$count['num'])
 			->render();
 		echo $view;	
	}

	/**
	 * 下载报表
	 */
	public function action_down()
	{
 		set_time_limit(0);
 		ini_set('memory_limit','512M');
 		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
 		$this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
 		$objPHPExcel      = new PHPExcel();
 		$objPHPExcel->getProperties()->setCreator("YKS")
 		->setLastModifiedBy("YKS")
 		->setTitle("Office 2003 XLSX Test Document")
 		->setSubject("Office 2003 XLSX Test Document")
 		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
 		->setKeywords("office 2003 openxml php")
 		->setCategory("Test result file");
 		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
 		$objActSheet->setCellValue('A1', 'SKU');
 		$objActSheet->setCellValue('B1', '中文名称');
 		$objActSheet->setCellValue('C1', '日均销量');
 		$objActSheet->setCellValue('D1', '7天销量');
 		$objActSheet->setCellValue('E1', '14天销量');
 		$objActSheet->setCellValue('F1', '21天销量');
 		$objActSheet->setCellValue('G1', '28天销量');
 		$objActSheet->setCellValue('H1', '采购定价单价');
 		$objActSheet->setCellValue('I1', '供应商采购单价');
		$and_where=$orderby='';
	 	if(isset($_GET['sku'])&&$_GET['sku']){
	 		$and_where=" AND sku='".$_GET['sku']."'"; 
	 	}
	 	if(isset($_GET['sortcondition'])&&$_GET['sortcondition']){
	 		$order=" order by ".$_GET['sortcondition']." desc";
	 		if(isset($_GET['order'])&&$_GET['order']){
		 		$order=$_GET['order']==1?'asc':'desc';
	 			$orderby=" order by ".$_GET['sortcondition']." ".$order;
	 		}
	 	}
		$sql="select * from product_attached where 1 $and_where $orderby  limit 5000";
	 	$sales=DB::query(Database::SELECT, $sql)->execute('readonlyerp')->as_array();
		$i=0;
        foreach($sales as $sale){
 			$key=$i+2;
 			$i++;
 			$objActSheet->setCellValue('A'.$key, $sale['sku']);
	 		//$objActSheet->setCellValue('B'.$key, $sale['sku_name']);
	 		$objActSheet->setCellValue('C'.$key, $sale['daysales']);
			$objActSheet->setCellValue('D'.$key, $sale['daysales7']);
	 		$objActSheet->setCellValue('E'.$key, $sale['daysales14']);
	 		$objActSheet->setCellValue('F'.$key, $sale['daysales21']);
	 		$objActSheet->setCellValue('G'.$key, $sale['daysales28']);
            //START     ----------------------------------CWY更新代码-------------------------------------
            $purchase_price = Newerpclientapi::getSupplierskuPrice($sale['sku']);
            if(!empty($purchase_price[0]['price'])){
                $sales[$key]['min_purchase_price']=$purchase_price[0]['price'];
            }else{
                $sales[$key]['min_purchase_price']=0;
            }
            $min=Skusystemapi::getSku($sale['sku']);
            if(!empty($min)){
                $price = Skusystemapi::getSkuPrice($sale['sku']);
                $cnname = Skusystemapi::getSkuCnname($sale['sku']);
                $sales[$key]['purchase_price']=$price[0]['num'];
                $sales[$key]['sku_name']=$cnname[0]['name'];
            }else{
                $sales[$key]['purchase_price']=0;
                $sales[$key]['sku_name']='-';
            }
//        	$sql="select price from erp_providerproducts where iproductID='".$sale['sku']."' order by price limit 1";
//	        $purchase_price=DB::query(Database::SELECT, $sql)->execute('readonlyerp')->current();
//        	if(isset($purchase_price['price'])){
//	        	$sale['min_purchase_price']=$purchase_price['price'];
//        	}else{
//        		$sale['min_purchase_price']=0;
//        	}
//        	$sql="select products_name_cn,products_values from erp_products_data where products_sku='".$sale['sku']."' limit 1";
//        	$min=DB::query(Database::SELECT, $sql)->execute('readonlyerp')->current();
//        	if(isset($min['products_values'])){
//	        	$sale['purchase_price']=$min['products_values'];
//	        	$sale['sku_name']=$min['products_name_cn'];
//        	}else{
//        		$sale['purchase_price']='';
//        		$sale['sku_name']='';
//        	}
            //END     ----------------------------------CWY更新代码-------------------------------------
	    	$objActSheet->setCellValue('B'.$key, $sale['sku_name']);
	 		$objActSheet->setCellValue('H'.$key, $sale['min_purchase_price']);
	 		$objActSheet->setCellValue('I'.$key, $sale['purchase_price']);
			unset($sale['sku_name']);unset($sale['min_purchase_price']);unset($sale['purchase_price']);
 		}
 		$objPHPExcel->getActiveSheet()->setTitle('产品月销量统计报表');
		$objPHPExcel->setActiveSheetIndex(0);
 		$day=date("m-d");
 		$filename=$day.'产品月销量统计报表.xls';
        ob_end_clean();
		header("Content-Type: application/vnd.ms-excel; charset=utf-8");
		header('Content-Disposition: attachment;filename='.$filename);
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;		
	}
}
